package com.c2soft.nepalcmx.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.sql.DataSource;





import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.c2soft.nepalcmx.command.NetPositionCommand;


public class NetPositionDAO {

	@Autowired
	private DataSource dataSource;

	private JdbcTemplate jdbcTemplate;
	
	
	    
	public List<NetPositionCommand> findAllNetPosition(String userId,String ptype) {
	
		String sql = "SELECT SUM(ql),price,AVG(ql*price),symbol,ptype,user_Id FROM ordermgmt o where user_id=? "
				+ "and ptype=? and type='BUY' group by symbol ";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<NetPositionCommand> netPositions=jdbcTemplate.query(sql,new Object[] {userId,ptype},
				new NetPositionMapper());
		
		return netPositions;
	}
	
	private final class NetPositionMapper implements RowMapper<NetPositionCommand> {

	    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	NetPositionCommand netpos = new NetPositionCommand();
	    	netpos.setLot1(rs.getString(1));
	    	netpos.setPrice1(rs.getString(2));
	    	netpos.setPrice3(rs.getString(3));
	    	netpos.setSymbol(rs.getString(4));
	    	netpos.setPtype(rs.getString(5));
	    	netpos.setUserId(rs.getString(6));
	    	String sql1 ="SELECT SUM(ql),price,AVG(ql*price),symbol FROM ordermgmt o where type='SELL' and user_Id='"+
	    	netpos.getUserId()+"' and ptype='"+netpos.getPtype()+"' group by symbol";
	    	List netPositions=jdbcTemplate.query(sql1,
	    			new NetPositionMapper1());
	    	
	    	Iterator itr=netPositions.iterator();
	    	while(itr.hasNext()){
	    		NetPositionCommand npc=(NetPositionCommand)itr.next();
	    		if(npc.getSymbol().equals(netpos.getSymbol())){
	    		netpos.setLot2(npc.getLot2());
	    		netpos.setPrice2(npc.getPrice2());
	    		netpos.setPrice4(npc.getPrice4());
	    		
	    		}
	    	}
	        return netpos;
	    } 
	}
	   
	    private final class NetPositionMapper1 implements RowMapper<NetPositionCommand> {

		    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	NetPositionCommand netpos = new NetPositionCommand();
		    	netpos.setLot2(rs.getString(1));
		    	netpos.setPrice2(rs.getString(2));
		    	netpos.setPrice4(rs.getString(3));
		    	netpos.setSymbol(rs.getString(4));
		        return netpos;
		    } 
	    }

	  /******************************************/  
	    public List<NetPositionCommand> findAllNetPositions(String userId) {
	    	
			String sql = "SELECT SUM(ql),price,AVG(ql*price),symbol,ptype,user_Id FROM ordermgmt o where user_id=?  and type='BUY' group by symbol,ptype ";
			jdbcTemplate = new JdbcTemplate(dataSource);
			List<NetPositionCommand> netPositions1=jdbcTemplate.query(sql,new Object[] {userId},
					new NetPositionMapper2());
			
			return netPositions1;
		}
		
		private final class NetPositionMapper2 implements RowMapper<NetPositionCommand> {

		    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	NetPositionCommand netpos = new NetPositionCommand();
		    	netpos.setLot1(rs.getString(1));
		    	netpos.setPrice1(rs.getString(2));
		    	netpos.setPrice3(rs.getString(3));
		    	netpos.setSymbol(rs.getString(4));
		    	netpos.setPtype(rs.getString(5));
		    	netpos.setUserId(rs.getString(6));
		    	String sql1 = "SELECT SUM(ql),price,AVG(ql*price),symbol FROM ordermgmt o where type='SELL'  and user_Id='"+netpos.getUserId()+"' and ptype='"+netpos.getPtype()+"' group by symbol";
		    	List netPositions=jdbcTemplate.query(sql1,
		    			new NetPositionMapper3());
		    	Iterator itr=netPositions.iterator();
		    	while(itr.hasNext()){
		    		NetPositionCommand npc=(NetPositionCommand)itr.next();
		    		if(npc.getSymbol().equals(netpos.getSymbol())){
		    		netpos.setLot2(npc.getLot2());
		    		netpos.setPrice2(npc.getPrice2());
		    		netpos.setPrice4(npc.getPrice4());
		    		}
		    	}
		        return netpos;
		    } 
		}
		   
		    private final class NetPositionMapper3 implements RowMapper<NetPositionCommand> {

			    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
			    	NetPositionCommand netpos = new NetPositionCommand();
			    	netpos.setLot2(rs.getString(1));
			    	netpos.setPrice2(rs.getString(2));
			    	netpos.setPrice4(rs.getString(3));
			    	netpos.setSymbol(rs.getString(4));
			        return netpos;
			    } 
		    }
      
/*****************************************************/
		    public List<NetPositionCommand> findAllNetPosition1(String brokeId,String ptype) {
		    	
				String sql = "SELECT SUM(ql),price,AVG(ql*price),symbol,ptype,brokeId FROM ordermgmt b where brokeId=? and user_id ='' and ptype=? and type='BUY' group by symbol; ";
				jdbcTemplate = new JdbcTemplate(dataSource);
				List<NetPositionCommand> netPositions=jdbcTemplate.query(sql,new Object[] {brokeId,ptype},
						new NetPositionMapper4());
				
				return netPositions;
			}
			
			private final class NetPositionMapper4 implements RowMapper<NetPositionCommand> {

			    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
			    	NetPositionCommand netpos = new NetPositionCommand();
			    	netpos.setLot1(rs.getString(1));
			    	netpos.setPrice1(rs.getString(2));
			    	netpos.setPrice3(rs.getString(3));
			    	netpos.setSymbol(rs.getString(4));
			    	netpos.setPtype(rs.getString(5));
			    	netpos.setBrokeId(rs.getString(6));
			    	String sql1 = "SELECT SUM(ql),price,AVG(ql*price),symbol FROM ordermgmt b where type='SELL' and user_id =''  and brokeId='"+netpos.getBrokeId()+"' and ptype='"+netpos.getPtype()+"' group by symbol";
			    	List netPositions=jdbcTemplate.query(sql1,
			    			new NetPositionMapper5());
			    	Iterator itr=netPositions.iterator();
			    	while(itr.hasNext()){
			    		NetPositionCommand npc=(NetPositionCommand)itr.next();
			    		if(npc.getSymbol().equals(netpos.getSymbol())){
			    		netpos.setLot2(npc.getLot2());
			    		netpos.setPrice2(npc.getPrice2());
			    		netpos.setPrice4(npc.getPrice4());
			    	}}
			    	return netpos;
			    } 
			}
			   
			    private final class NetPositionMapper5 implements RowMapper<NetPositionCommand> {

				    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				    	NetPositionCommand netpos = new NetPositionCommand();
				    	netpos.setLot2(rs.getString(1));
				    	netpos.setPrice2(rs.getString(2));
				    	netpos.setPrice4(rs.getString(3));
				    	netpos.setSymbol(rs.getString(4));
				        return netpos;
				    } 
		}

	/*********************************************************/		    
			    public List<NetPositionCommand> findAllNetPositions1(String brokeId) {
			    	
					String sql = "SELECT SUM(ql),price,AVG(ql*price),symbol,ptype,brokeId FROM ordermgmt o where brokeId=? and user_id ='' and type='BUY' group by symbol,ptype; ";
					jdbcTemplate = new JdbcTemplate(dataSource);
					List<NetPositionCommand> netPositions1=jdbcTemplate.query(sql,new Object[] {brokeId},
							new NetPositionMapper6());
					
					return netPositions1;
				}
				
				private final class NetPositionMapper6 implements RowMapper<NetPositionCommand> {

				    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				    	NetPositionCommand netpos = new NetPositionCommand();
				    	netpos.setLot1(rs.getString(1));
				    	netpos.setPrice1(rs.getString(2));
				    	netpos.setPrice3(rs.getString(3));
				    	netpos.setSymbol(rs.getString(4));
				    	netpos.setPtype(rs.getString(5));
				    	netpos.setBrokeId(rs.getString(6));
				    	String sql1 = "SELECT SUM(ql),price,AVG(ql*price),symbol FROM ordermgmt o where type='SELL' and user_id =''  and brokeId='"+netpos.getBrokeId()+"' and ptype='"+netpos.getPtype()+"' group by symbol";
				    	List netPositions=jdbcTemplate.query(sql1,
				    			new NetPositionMapper7());
				    	
				    	Iterator itr=netPositions.iterator();
				    	while(itr.hasNext()){
				    		NetPositionCommand npc=(NetPositionCommand)itr.next();
				    		if(npc.getSymbol().equals(netpos.getSymbol())){
				    		netpos.setLot2(npc.getLot2());
				    		netpos.setPrice2(npc.getPrice2());
				    		netpos.setPrice4(npc.getPrice4());
				    				    		
				    		}
				    	}
				        return netpos;
				    } 
				}
				   
				    private final class NetPositionMapper7 implements RowMapper<NetPositionCommand> {

					    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
					    	NetPositionCommand netpos = new NetPositionCommand();
					    	netpos.setLot2(rs.getString(1));
					    	netpos.setPrice2(rs.getString(2));
					    	netpos.setPrice4(rs.getString(3));
					    	netpos.setSymbol(rs.getString(4));
					        return netpos;
					    } 
			}
		      
/**************************************************************/
public List<NetPositionCommand> findAllNetPosition2(String ptype) {
				    	
String sql = "SELECT SUM(ql),price,AVG(ql*price),symbol,ptype FROM ordermgmt o where user_id ='' and brokeId =''  and ptype=? and type='BUY' group by symbol; ";
						jdbcTemplate = new JdbcTemplate(dataSource);
List<NetPositionCommand> netPositions=jdbcTemplate.query(sql,new Object[] {ptype},new NetPositionMapper8());
	return netPositions;
}
					
private final class NetPositionMapper8 implements RowMapper<NetPositionCommand> {

	 public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
			NetPositionCommand netpos = new NetPositionCommand();
			netpos.setLot1(rs.getString(1));
			netpos.setPrice1(rs.getString(2));
			netpos.setPrice3(rs.getString(3));
			netpos.setSymbol(rs.getString(4));
			netpos.setPtype(rs.getString(5));
			String sql1 = "SELECT SUM(ql),price,AVG(ql*price),symbol FROM ordermgmt o where type='SELL'  and  user_id ='' and brokeId ='' and ptype='"+netpos.getPtype()+"' group by symbol";
			List netPositions=jdbcTemplate.query(sql1,new NetPositionMapper9());
					    	
			Iterator itr=netPositions.iterator();
			while(itr.hasNext()){
				NetPositionCommand npc=(NetPositionCommand)itr.next();
				if(npc.getSymbol().equals(netpos.getSymbol())){
					 netpos.setLot2(npc.getLot2());
					 netpos.setPrice2(npc.getPrice2());
					 netpos.setPrice4(npc.getPrice4());
				}
				}
				return netpos;
					    } 
					}
					   
		 private final class NetPositionMapper9 implements RowMapper<NetPositionCommand> {

			 public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				NetPositionCommand netpos = new NetPositionCommand();
					netpos.setLot2(rs.getString(1));
					netpos.setPrice2(rs.getString(2));
					netpos.setPrice4(rs.getString(3));
					netpos.setSymbol(rs.getString(4));
					return netpos;
						    } 
				}
/***************************************************************/
					    
 public List<NetPositionCommand> findAllNetPositions2() {
					    	
String sql = "SELECT SUM(ql),price,AVG(ql*price),symbol,ptype FROM ordermgmt o where user_id ='' "
		+ "and brokeId =''  and type='BUY' group by symbol,ptype; ";
							jdbcTemplate = new JdbcTemplate(dataSource);
							List<NetPositionCommand> netPositions1=jdbcTemplate.query(sql,
									new NetPositionMapper10());
							return netPositions1;
}
						
private final class NetPositionMapper10 implements RowMapper<NetPositionCommand> {

	public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		NetPositionCommand netpos = new NetPositionCommand();
		netpos.setLot1(rs.getString(1));
		netpos.setPrice1(rs.getString(2));
		netpos.setPrice3(rs.getString(3));
		netpos.setSymbol(rs.getString(4));
		netpos.setPtype(rs.getString(5));
		String sql1 = "SELECT SUM(ql),price,AVG(ql*price),symbol FROM ordermgmt o where type='SELL' and user_id='' "
				+ "and brokeId=''  and ptype='"+netpos.getPtype()+"' group by symbol";
		List netPositions=jdbcTemplate.query(sql1,new NetPositionMapper11());
		Iterator itr=netPositions.iterator();
		while(itr.hasNext()){
		NetPositionCommand npc=(NetPositionCommand)itr.next();
		if(npc.getSymbol().equals(netpos.getSymbol())){
			netpos.setLot2(npc.getLot2());
			netpos.setPrice2(npc.getPrice2());
			netpos.setPrice4(npc.getPrice4());
		}
		}
		     return netpos;
		   } 
	}
						   
	private final class NetPositionMapper11 implements RowMapper<NetPositionCommand> {

							    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
							    	NetPositionCommand netpos = new NetPositionCommand();
							    	netpos.setLot2(rs.getString(1));
							    	netpos.setPrice2(rs.getString(2));
							    	netpos.setPrice4(rs.getString(3));
							    	netpos.setSymbol(rs.getString(4));
							        return netpos;
							    } 
					}
				      

}